package com.info.zhiduoduo.repository.mapper.partner;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.partner.CommissionRecordDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerCompanyTotalBrokerageDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerCompanyTotalProjectAmountDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerMonthBrokerageDto;
import com.info.zhiduoduo.repository.entity.partner.PartnerBrokerage;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 合伙人项目佣金记录表 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-06-17
 */
public interface PartnerBrokerageMapper extends BaseMapper<PartnerBrokerage> {

	@Select(" select IFNULL(SUM(brokerage), 0) from zdd_partner_brokerage " +
			" where partnerid=#{partnerid} " +
			" and IF(#{state}=0,1=1,state=#{state}) and deleted = 0 ")
	BigDecimal getTotalBrokerageWithState(@Param("partnerid") int partnerid, @Param("state") int state);

	@Select(" select IFNULL(SUM(brokerage), 0) from zdd_partner_brokerage " +
			" where partnerid=#{partnerid} " +
			" and IF(#{state}=0,1=1,state=#{state}) and partner_type != 3 and deleted = 0 ")
	BigDecimal getTotalNormalBrokerageWithState(@Param("partnerid") int partnerid, @Param("state") int state);

	@Select(" select IFNULL(SUM(brokerage), 0) from zdd_partner_brokerage " +
			" where partnerid=#{partnerid} " +
			" and IF(#{state}=0,1=1,state=#{state})" +
			" and partner_type = 3 and deleted = 0 ")
	BigDecimal getTotalCrowdsourceBrokerageByPartnerId(@Param("partnerid") int partnerid, @Param("state") int state);

	@Select(" select IFNULL(SUM(brokerage), 0) from zdd_partner_brokerage " +
			" where partnerid=#{partnerid} " +
			" and IF(#{state}=0,1=1,state=#{state})" +
			" and partner_type = 6 and deleted = 0 ")
	BigDecimal getOtherBrokerageByPartnerId(@Param("partnerid") int partnerid, @Param("state") int state);

	@Select("select * from zdd_partner_brokerage where partnerid = #{partnerid} and state = #{state} and deleted = 0  order by createtime desc")
	List<PartnerBrokerage> getPartnerAllCommission(@Param("partnerid") int partnerid, @Param("state") int state);

	@Select(" select * from zdd_partner_brokerage " +
			" where IF(#{compid}=0,1=1,compid = #{compid}) " +
			" and partnerid = #{partnerid} and state in(2,3,4) " +
			" and brokerage > 0 " +
			" and deleted = 0 " +
			" order by brokerageid desc ")
	List<PartnerBrokerage> getPartnerAndCompanyAllCommission(@Param("compid") int compid, @Param("partnerid") int partnerid);

	@Select(" select SUM(brokerage) from zdd_partner_brokerage " +
			" where IF(#{compid}=0,1=1,compid = #{compid}) " +
			" and partnerid = #{partnerid} and state in(2,3,4) " +
			" and deleted = 0 ")
	BigDecimal getTotalBrokerageByCompid(@Param("compid") int compid, @Param("partnerid") int partnerid);

	@Select(" select IFNULL(SUM(brokerage),0) " +
			" from zdd_partner_brokerage " +
			" where state >= 2 " +
			" and partner_type in(1,2,4) " +
			" and IF(#{year}=0,YEAR(createtime) = YEAR(NOW()),YEAR(createtime) = #{year}) " +
			" and IF(#{quarter}=0,QUARTER(createtime) = QUARTER(NOW()),QUARTER(createtime) = #{quarter}) " +
			" and deleted = 0 ")
	BigDecimal getTotalBrokerageByQuarter(int year, int quarter);

	@Select(" select IFNULL(SUM(brokerage),0) " +
			" from zdd_partner_brokerage " +
			" where state >= 2 " +
			" and partner_type in(1,2,4) " +
			" and IF(#{year}=0,YEAR(createtime) = YEAR(NOW()),YEAR(createtime) = #{year}) " +
			" and IF(#{month}=0,MONTH(createtime) = MONTH(NOW()),MONTH(createtime) = #{month}) " +
			" and deleted = 0 ")
	BigDecimal getTotalBrokerageByMonth(int year, int month);

	@Select(" select * from zdd_partner_brokerage " +
			" where wdid=#{wdid} " +
			" and deleted = 0 ")
	List<PartnerBrokerage> getBrokerageListByWdid(@Param("wdid") int wdid);

	//根据合伙人ID和状态取出所需数据
	@Select("select * from zdd_partner_brokerage where partnerid = #{partnerid} and state = #{state} and partner_type != 3 and deleted = 0 ")
	List<PartnerBrokerage> getNormalBrokerageListByPartnerIdAndState(@Param("partnerid") int partnerid, @Param("state") int state);

	//根据合伙人ID和状态取出所需数据
	@Select("select * from zdd_partner_brokerage where partnerid = #{partnerid} and state = #{state} and partner_type = 3 and deleted = 0 ")
	List<PartnerBrokerage> getCrowdsourceBrokerageListByPartnerIdAndState(@Param("partnerid") int partnerid, @Param("state") int state);

	@Select(" select pb.* " +
			" from zdd_partner_brokerage pb " +
			" where projectid=#{projectid} " +
			" and project_type = #{projectType} " +
			" and if(#{type}=0,1=1,partner_type=#{type}) " +
			" and pb.deleted = 0 ")
	List<PartnerBrokerage> getZhiduoduoProjectBrokerageList(@Param("projectid") int projectid, @Param("projectType") int projectType, @Param("type") int type);

	@Select(" select pb.* " +
			" from zdd_partner_brokerage pb " +
			" where projectid=#{invoiceid} " +
			" and project_type = 3 " +
			" and if(#{type}=0,1=1,partner_type=#{type}) " +
			" and pb.deleted = 0 ")
	List<PartnerBrokerage> getTaxerInvoiceBrokerageList(@Param("invoiceid") int invoiceid, @Param("type") int type);

	@Select(" select pb.* " +
			" from zdd_partner_brokerage pb " +
			" where projectid=#{projectid} " +
			" and project_type = #{projectType} " +
			" and if(#{type}=0,1=1,partner_type=#{type}) " +
			" and pb.deleted = 0 ")
	List<PartnerBrokerage> getCrowdsourceProjectBrokerageList(@Param("projectid") int projectid, @Param("projectType") int projectType, @Param("type") int type);

	@Select("SELECT pb.brokerageid,pb.createtime,pb.brokerageno,pb.projectno,pb.tradeno,pb.remark, " +
			"pb.state,pb.projectdealamount,pb.projectservicecharge,pb.projecttaxfee,pb.brokerage, " +
			"pb.partner_type AS partnerType,pb.settle_type,pb.remit_type AS remitType,pb.project_type," +
			"CASE project_type WHEN 3 THEN ms.username ELSE c.compname END as compname, " +
			"pa.realname AS partnerName,ua.realname AS username,pa.partner_type as partnerAccountType, " +
			"pb.voucher_path, pb.adminid,pb.ratio " +
			"FROM zdd_partner_brokerage pb " +
			"LEFT JOIN zdd_company c ON c.compid = pb.compid and pb.project_type != 3 " +
			"LEFT JOIN zdd_maker_studio ms ON ms.userid = pb.userid and pb.project_type = 3 " +
			"LEFT JOIN zdd_partner_account pa ON pa.partnerid = pb.partnerid " +
			"LEFT JOIN zdd_maker_account ua ON ua.userid = pb.userid " +
			"WHERE 1=1  ${whereStr} and pb.deleted = 0 ORDER BY pb.createtime DESC")
	List<CommissionRecordDto> getAllCommissionRecord(@Param("whereStr") String whereStr);

	@Select("SELECT IFNULL(SUM(pb.brokerage), 0) FROM zdd_partner_brokerage pb " +
			"LEFT JOIN zdd_company c ON c.compid = pb.compid " +
			"LEFT JOIN zdd_partner_account pa ON pa.partnerid = pb.partnerid " +
			"LEFT JOIN zdd_maker_account ua ON ua.userid = pb.userid " +
			"WHERE 1=1  ${whereStr} and pb.deleted = 0")
	double statisticsAmountByType(@Param("whereStr") String whereStr);

	@Select("SELECT IFNULL(SUM(pb.brokerage), 0) FROM zdd_partner_brokerage pb " +
			"WHERE pb.state = #{state} and pb.partner_type = #{partnerType} AND pb.project_type =#{projectType} and pb.deleted = 0")
	BigDecimal getUnTurnoverCommissionAmount(@Param("state") int state,@Param("partnerType")int partnerType,@Param("projectType")int projectType);

	@Select("SELECT pb.partnerid,pb.brokerageid,pb.createtime,pb.brokerageno,pb.projectno,pb.tradeno, " +
			"pb.state,pb.projectdealamount,pb.projectservicecharge,pb.projecttaxfee,pb.brokerage, " +
			"pb.partner_type AS partnerType,pb.settle_type,pb.remit_type AS remitType,c.compname, " +
			"pa.realname AS partnerName,ua.realname AS username,pa.partner_type as partnerAccountType " +
			"FROM zdd_partner_brokerage pb " +
			"LEFT JOIN zdd_company c ON c.compid = pb.compid " +
			"LEFT JOIN zdd_partner_account pa ON pa.partnerid = pb.partnerid " +
			"LEFT JOIN zdd_maker_account ua ON ua.userid = pb.userid " +
			"WHERE pb.brokerageid = #{brokerageid} ")
	CommissionRecordDto getCommissionRecord(@Param("brokerageid") int brokerageid);

	@Select(" select SUM(pb.brokerage) from zdd_partner_brokerage pb " +
			" where IF(#{compid}=0,1=1,pb.compid=#{compid}) and pb.state=2 and pb.partner_type != 3  and pb.deleted = 0 ")
	BigDecimal getunwithdrawNormalBrokerageAmount(@Param("compid") int compid);

	@Select(" select SUM(pb.brokerage) from zdd_partner_brokerage pb " +
			" where IF(#{compid}=0,1=1,pb.compid=#{compid}) and pb.state=2 and pb.partner_type = 3 and pb.deleted = 0 ")
	BigDecimal getTotalCrowdsourceBrokerageByCompid(@Param("compid") int compid);

	@Select(" select pb.partnerid,pa.realname AS partnerName,sum(pb.brokerage) brokerage " +
			" from zdd_partner_brokerage pb " +
			" join zdd_partner_account pa on pa.partnerid = pb.partnerid " +
			" where pb.deleted = 0 " +
			" and DATE(pb.createtime) >= DATE(#{date}) " +
			" and pb.remit_type = 1 " +
			" and pb.settle_type = 1 " +
			" GROUP BY pb.partnerid " +
			" having sum(pb.brokerage) >= #{amount} ")
	List<PartnerMonthBrokerageDto> listBrokerageMonthExceed(@Param("date") Date date, @Param("amount") int amount);

	@Select(" select COUNT(*) " +
			" from zdd_partner_brokerage pb " +
			" where projectid = #{projectid} " +
			" and partnerid = #{partnerid} " +
			" and project_type = 1 " +
			" and pb.deleted = 0 ")
	boolean checkPartnerBrokerageExists(int projectid, int partnerid);

	@Select(" select pb.partnerid,pb.compid,c.compname," +
			" SUM(IF(pb.settle_type=1,pb.brokerage,0)) AS autoTotalBrokerage," +
			" SUM(IF(pb.settle_type=2,pb.brokerage,0)) AS manualTotalBrokerage " +
			" from zdd_partner_brokerage pb " +
			" join zdd_company c on c.compid = pb.compid " +
			" where pb.partnerid = #{partnerid} " +
			" and pb.state = 4 ")
	List<PartnerCompanyTotalBrokerageDto> sumBrokerageBySettleType(int partnerid);

	@Select(" select pb.partnerid,pb.compid,c.compname," +
			" SUM(IF(pb.state=4,pb.projectdealamount,0)) AS totalRemittedProjectAmount," +
			" SUM(IF(pb.state in(2,3),pb.projectdealamount,0)) AS totalUnremittedProjectAmount " +
			" from zdd_partner_brokerage pb " +
			" join zdd_company c on c.compid = pb.compid " +
			" where pb.partnerid = #{partnerid} " +
			" group by pb.compid ")
	List<PartnerCompanyTotalProjectAmountDto> sumTotalProjectAmountByRemitState(int partnerid);

	@Select(" select pb.* " +
			" from zdd_partner_brokerage pb " +
			" join zdd_company_project cp on cp.projectid = pb.projectid and cp.state = 20 and pb.project_type = 1 " +
			" where pb.partnerid = #{partnerid} " +
			" and pb.state = 1 " +
			" and pb.partner_type = 1 " +
			" and pb.deleted = 0 ")
	List<PartnerBrokerage> listNotSettledBrokerage(int partnerid);
}
